<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
SELECT T.* ,
(SELECT COUNT(1)
FROM C1_NAVIGATION s
WHERE NOW() BETWEEN IFNULL(s.BEGIN_DATE, NOW()) AND IFNULL(s.END_DATE, NOW()) AND s.PARENT_ID = t.id) AS childTotal
FROM C1_NAVIGATION T
WHERE 1 = 1
AND (NOW() BETWEEN IFNULL(T.BEGIN_DATE, NOW()) AND IFNULL(T.END_DATE, NOW()))
AND <#if (StringUtils.isNotBlank(parentCode))>(t.parent_code = :parentCode or t.parent_id = :parentCode)<#else>t.parent_id IS NULL</#if>
AND (T.site_code = :siteCode OR t.site_id = :siteCode)
AND (T.type_code = :typeCode OR t.type_id = :typeCode)
ORDER BY t.ORDER_NUM
